using System;
using System.Collections.Generic;
using System.Text;
//using Microsoft.Office.Core;
using Excel = Microsoft.Office.Interop.Excel;
using System.Windows.Forms;
using System.Collections;
using System.Collections.Specialized;
using System.Data;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

namespace eBirdAttend
{
    public class EbExcelAuto
    {
        Microsoft.Office.Interop.Excel.Application _excel = null;
        Microsoft.Office.Interop.Excel.Workbook _theWorkbook;
        Microsoft.Office.Interop.Excel.Sheets _sheets;
        Microsoft.Office.Interop.Excel.Worksheet _WorkSheet1;
        public int _CurRow, _CurCol;
        int _FoundRow, _FoundCol;
        string _FileName;
        public void Init()
        {
            _excel = new Microsoft.Office.Interop.Excel.Application();
            
        }
        public void OpenExcelFile(string FileName)
        {
            try
            {
                _theWorkbook = _excel.Workbooks.Open(FileName, Type.Missing, false, Type.Missing, Type.Missing, Type.Missing, false, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                _sheets = _theWorkbook.Worksheets;
                _FileName = FileName;
                _WorkSheet1 = (Microsoft.Office.Interop.Excel.Worksheet)_sheets.get_Item(1);
            }
            catch (Exception exp)
            {
                MessageBox.Show("Make sure init function is called or filename is not correct");
            }
        }
        public void SetValue(int Row, int Col, string strValue)
        {
            _WorkSheet1.Cells[Row, Col] = strValue;

        }
        public void MakeColBold(int Row, int Col)
        {
            Microsoft.Office.Interop.Excel.Range Range1 = (Microsoft.Office.Interop.Excel.Range)_WorkSheet1.Cells[Row, Col];
            Range1.Font.Bold = true;

        }
        public void PrintPreview()
        {
            _WorkSheet1.PrintPreview(false);

        }
        public void Print()
        {
            _WorkSheet1.PrintOut(null, null, 1, true, true, null, null, null);
        }
        public void SetValue(string strValue)
        {
            _WorkSheet1.Cells[_CurRow, _CurCol] = strValue;

        }
        public void SaveAs(string SaveAsFile)
        {
            try
            {
               
                System.Windows.Forms.SaveFileDialog FileDlg = new System.Windows.Forms.SaveFileDialog();
                if ((SaveAsFile == null) || (SaveAsFile == ""))
                {
                    char[] Seperator = { '\\', '.' ,'/'};
                    char[] Seperator1 = { '-' ,'/'};

                    string[] FileParts = _FileName.Split(Seperator);
                    int LastItem = FileParts.GetLength(0);
                    string FileToSave = "";
                    if (LastItem > 0)
                    {
                        FileToSave = FileParts[LastItem - 2].ToString();
                        //FileToSave = FileToSave + DateTime.Today.ToShortDateString().ToString();
                        string[] DateParts = DateTime.Today.ToString("dd/MM/yyyy").Split(Seperator1);
                        FileToSave = FileToSave + DateParts[0].ToString() +"-" + DateParts[1].ToString() + "-" + DateParts[2].ToString();
                    }
                    FileDlg.DefaultExt = ".xlsx";
                    FileDlg.FileName = FileToSave + ".xlsx";
                    //FileDlg.ShowDialog();

                    SaveAsFile = FileDlg.FileName.ToString();
                }

                _theWorkbook.SaveAs(_FileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);// Type.Missing, Type.Missing, Type.Missing);
            }
            catch (Exception ex)
            {
            }
        }
        public bool SearchText(string toSearch, int SearchBeginRow, int SearchBeginCol, int SerchUptoRow, int SearchUptoCol)
        {
            string colString=null;
            try
            {
            for (int i = SearchBeginRow; i < SerchUptoRow; ++i)
            {
                for (int j = SearchBeginCol; j < SearchUptoCol; ++j)
                {

                    if (((Microsoft.Office.Interop.Excel.Range)_WorkSheet1.Cells[i, j]).Value2 != null)
                        {
                            colString = ((Microsoft.Office.Interop.Excel.Range)_WorkSheet1.Cells[i, j]).Value2.ToString();
              
                        }
                             
                    if (colString == toSearch)
                    {
                        _FoundRow = i; _FoundCol = j;
                        return true;
                    }
                }

            }
        }
        catch (Exception exp)
        {
        }

            return false;
        }


        public void FillExcellSheetFromDatagrid(string strPretext, GridView dataGridView,string strGroupByField)
        {
            try
            {

                string strGroupByFieldVal = "", strGroupByFieldValOld = "";
                int iGroupByColIndexInDGV = 0;
                int i = 0, j = 0;
                string curColText = "";

                StringCollection strHeaderCol = new StringCollection();
                if (!MoveCurPosToText(strPretext, 1, 1, 100, 100))
                    MessageBox.Show("Pretext 'START' stored in the Hidden Row to indicate the start of Data area is missing.\r\n");
                ++_CurRow;
                j = 1;
                while (j < 50)
                {
                    if (!GetColumnText(_CurRow, j, ref curColText)) break;
                    if (curColText == "") break;
                   // MessageBox.Show(curColText);
                    strHeaderCol.Add(curColText);
                    ++j;


                }
                iGroupByColIndexInDGV = -1;
                for (j = 0; j < dataGridView.Columns.Count; ++j)
                {
                    if (dataGridView.Columns[j].HeaderText == strGroupByField)
                    {
                        iGroupByColIndexInDGV = j;
                        break;
                    }
                }
                ++_CurRow;
                for (i = 0; i < dataGridView.Rows.Count; ++i)
                {
                    if (iGroupByColIndexInDGV >= 0)
                    {
                        if (dataGridView.Rows[i].Cells[iGroupByColIndexInDGV].Text != null)
                        {
                            strGroupByFieldVal = dataGridView.Rows[i].Cells[iGroupByColIndexInDGV].Text.ToString();
                            if (strGroupByFieldVal != strGroupByFieldValOld)
                            {
                                _CurCol = 1;
                                SetValue(strGroupByField);
                                MakeColBold(_CurRow, _CurCol);
                                _CurCol = 2;
                                SetValue(strGroupByFieldVal);
                                MakeColBold(_CurRow, _CurCol);
                                strGroupByFieldValOld = strGroupByFieldVal;
                                ++_CurRow;
                            }
                        }
                    }
                    for (j = 0; j < dataGridView.Columns.Count; ++j)
                    {

                        int iColIndex = strHeaderCol.IndexOf(dataGridView.Columns[j].HeaderText);
                        if (iColIndex != -1)

                            // MessageBox.Show(dataGridView1.Rows[i].Cells[j].Value.ToString());
                            if (dataGridView.Rows[i].Cells[iColIndex].Text != null)
                            {
                                _CurCol = iColIndex + 1;
                                SetValue(dataGridView.Rows[i].Cells[j].Text.ToString());
                            }

                            else
                            {
                                string strMsg = "Not matching =" + dataGridView.Columns[j].HeaderText;
                                //strMsg=strMsg+","+strHeaderCol[j].ToString();
                                // MessageBox.Show(strMsg);
                            }
                    }
                    ++_CurRow;
                }
            }
            catch (Exception exp)
            {
                MessageBox.Show(exp.Message.ToString());
            }

        }

        public void FillExcellSheetFromDataset(string strPretext, DataSet _dataset, string strGroupByField)
        {
            try
            {
                DataTable dt = _dataset.Tables[0];

                string strGroupByFieldVal = "", strGroupByFieldValOld = "";
                int iGroupByColIndexInDGV = 0;
                int i = 0, j = 0;
                string curColText = "";

                StringCollection strHeaderCol = new StringCollection();
                if (!MoveCurPosToText(strPretext, 1, 1, 100, 100))
                {
                    //MessageBox.Show("Pretext 'START' stored in the Hidden Row to indicate the start of Data area is missing.\r\n");
                }
                ++_CurRow;
                j = 1;
                while (j < 50)
                {
                    if (!GetColumnText(_CurRow, j, ref curColText)) break;
                    if (curColText == "") break;
                    strHeaderCol.Add(curColText);
                    ++j;


                }
                iGroupByColIndexInDGV = -1;
                for (j = 0; j < dt.Columns.Count; ++j)
                {
                    if (dt.Columns[j].ColumnName == strGroupByField)
                    {
                        iGroupByColIndexInDGV = j;
                        break;
                    }
                }
                ++_CurRow;
                for (i = 0; i < dt.Rows.Count; ++i)
                {
                    if (iGroupByColIndexInDGV >= 0)
                    {
                        if (dt.Rows[i][iGroupByColIndexInDGV] != null)
                        {
                            strGroupByFieldVal = dt.Rows[i][iGroupByColIndexInDGV].ToString();
                            if (strGroupByFieldVal != strGroupByFieldValOld)
                            {
                                _CurCol = 1;
                                SetValue(strGroupByField);
                                MakeColBold(_CurRow, _CurCol);
                                _CurCol = 2;
                                SetValue(strGroupByFieldVal);
                                MakeColBold(_CurRow, _CurCol);
                                strGroupByFieldValOld = strGroupByFieldVal;
                                ++_CurRow;
                            }
                        }
                    }
                    for (j = 0; j < dt.Columns.Count; ++j)
                    {

                        int iColIndex = strHeaderCol.IndexOf(dt.Columns[j].ColumnName);
                        if (iColIndex != -1)

                            if (dt.Rows[i][iColIndex] != null)
                            {
                                _CurCol = iColIndex + 1;
                                SetValue(dt.Rows[i][j].ToString());
                            }

                            else
                            {
                                string strMsg = "Not matching =" + dt.Columns[j].ColumnName;

                            }
                    }
                    ++_CurRow;
                }
            }
            catch (Exception exp)
            {

            }

        }


        public bool MoveCurPosToText(string toSearch, int SearchBeginRow, int SearchBeginCol, int SerchUptoRow, int SearchUptoCol)
        {
            bool retVal;
            retVal = SearchText(toSearch, SearchBeginRow, SearchBeginCol, SerchUptoRow, SearchUptoCol);
            if (retVal)
            {
                _CurRow = _FoundRow;
                _CurCol = _FoundCol;
            }
            return retVal;

        }

        public bool IsInteger(string data)
        {
            bool result = true;

            try
            {
                double.Parse(data);
            }
            catch (FormatException)
            {
                result = false;
            }

            return result;
        }
        public bool GetColumnText(int Row, int Col, ref string  ColString)
        {
            ColString = "";
            try
            {
                ColString = ((Microsoft.Office.Interop.Excel.Range)_WorkSheet1.Cells[Row, Col]).Value2.ToString();
                bool status = IsInteger(ColString);
                if (status)
                {
                    double value = Convert.ToDouble(ColString);
                    DateTime columnName = DateTime.FromOADate((double)value);
                    ColString = columnName.ToLongTimeString();
                }
                return true;
            }
            catch (Exception exp)
            {
                return false;
            }
      
        }



        private void NAR(object o)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(o);
            }
            catch { }
            finally
            {
                o = null;
            }
        }

        public void Close()
        {
            NAR(_sheets);
            NAR(_WorkSheet1);
            _sheets = null;
            _WorkSheet1=null;
            _theWorkbook.Close(false, null, null);
            NAR(_theWorkbook);
            _theWorkbook = null;
            _excel.Quit();
            NAR(_excel);
            _excel = null;
            
       }


    }
}
